package com.generation.builder;

import com.generation.Bean.Constants;
import com.generation.Bean.FieldInfo;
import com.generation.Bean.TableInfo;
import com.generation.utils.PropertiesUtils;
import com.generation.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import java.sql.*;
import java.util.*;

/**
 * @author mcokingjay
 * @date 2023/4/2 - 21:05
 * 读取表
 */
public class BuilderTable {

    private static final Logger LOGGER= LoggerFactory.getLogger(BuilderTable.class);

    private static Connection CONN=null;

    private static String SQL_SHOW_TABLE_STATUS="show table status";

    private static String SQL_SHOW_TABLE_FIELDS="show full fields from %s";

    private static String SQL_SHOW_TABLE_INDEX="show index from %s";

    static {
        String driverName= PropertiesUtils.getString("db.driver.name");
        String url=PropertiesUtils.getString("db.url");
        String username=PropertiesUtils.getString("db.username");
        String password=PropertiesUtils.getString("db.password");
        try {
            Class.forName(driverName);
            CONN= DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.error("链接失败");
        }

    }

    //读table属性
    public static List<TableInfo> getTable(){
        PreparedStatement ps=null;
        ResultSet tableResult=null;
        List<TableInfo> tableInfoList=new ArrayList<>();
        try {
            ps=CONN.prepareStatement(SQL_SHOW_TABLE_STATUS);
            tableResult=ps.executeQuery();
            while (tableResult.next()){
                String tableName=tableResult.getString("name");
                String comment=tableResult.getString("comment");
                String beanName=tableName;
                //截取表名前缀
                if(Constants.IGNORE_TABLE_PREFIX){
                    beanName=tableName.substring(beanName.indexOf("_")+1);
                }
                beanName= StringUtils.processField(beanName,true);
                TableInfo tableInfo=new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName+Constants.SUFFIX_BEAN_PARMA);
                List<FieldInfo> fieldInfoList = readFieldInfo(tableInfo);

                tableInfo.setFieldInfoList(fieldInfoList);
                getKeyIndexInfo(tableInfo);
               // LOGGER.info("转换结果:{}", JsonUtils.convertObj2Json(tableInfo));
               // LOGGER.info("字段:{}",JsonUtils.convertObj2Json(fieldInfoList));
                tableInfoList.add(tableInfo);
            }
            return tableInfoList;
        }catch (Exception e){
            LOGGER.error("读取表失败");
        }finally {
            if(tableResult!=null){
                try {
                    tableResult.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(CONN!=null){
                try {
                    CONN.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    //读field属性
    private static List<FieldInfo> readFieldInfo(TableInfo tableInfo){
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<FieldInfo> fieldInfoList=new ArrayList<>();
        List<FieldInfo> extendList=new ArrayList<>();
        Boolean havaDateTime=false;
        Boolean haveDate=false;
        Boolean havBigDecimal=false;
        try {
            ps=CONN.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS,tableInfo.getTableName()));
            rs=ps.executeQuery();

            while (rs.next()){
                FieldInfo fieldInfo=new FieldInfo();

                String fieldName=rs.getString("field");
                String comment=rs.getString("comment");
                String type=rs.getString("type");
                String extra=rs.getString("extra");
                String pri=rs.getString("Key");
                if(type.indexOf("(")>0){
                    type=type.substring(0,type.indexOf("("));
                }
                String propertyName=StringUtils.processField(fieldName,false);


                fieldInfo.setFieldName(fieldName);
                fieldInfo.setComment(comment);
                fieldInfo.setSqlType(type);
                fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(extra)?true:false);
                if("PRI".equalsIgnoreCase(pri)){
                    fieldInfo.setPrimaryKey(propertyName);
                }
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(type));
                fieldInfoList.add(fieldInfo);

                if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPE,type)){
                    havaDateTime=true;
                }
                if(ArrayUtils.contains(Constants.SQL_DATE_TYPE,type)){
                    haveDate=true;
                }
                if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE,type)){
                    havBigDecimal=true;
                }
                //如果是String类型加上fuzzy
                if(ArrayUtils.contains(Constants.SQL_STRING_TYPE,type)){
                    FieldInfo fieldInfoFuzzy=new FieldInfo();
                    fieldInfoFuzzy.setJavaType(fieldInfo.getJavaType());
                    fieldInfoFuzzy.setPropertyName(propertyName+Constants.SUFFIX_BEAN_QUERY_FUZZY);
                    fieldInfoFuzzy.setFieldName(fieldName);
                    fieldInfoFuzzy.setSqlType(type);
                    extendList.add(fieldInfoFuzzy);
                }
                //日期类型加上start和end
                if(ArrayUtils.contains(Constants.SQL_DATE_TYPE,type)||ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPE,type)){

                    FieldInfo fieldInfoStart=new FieldInfo();
                    fieldInfoStart.setJavaType("String");
                    fieldInfoStart.setPropertyName(fieldInfo.getPropertyName()+Constants.SUFFIX_BEAN_QUERY_TIME_START);
                    fieldInfoStart.setFieldName(fieldName);
                    fieldInfoStart.setSqlType(type);
                    extendList.add(fieldInfoStart);
                    FieldInfo fieldInfoEnd=new FieldInfo();
                    fieldInfoEnd.setJavaType("String");
                    fieldInfoEnd.setPropertyName(fieldInfo.getPropertyName()+Constants.SUFFIX_BEAN_QUERY_TIME_END);
                    fieldInfoEnd.setFieldName(fieldName);
                    fieldInfoEnd.setSqlType(type);
                    extendList.add(fieldInfoEnd);
                }
                //LOGGER.info("javaType:{}",processJavaType(type));
            }
            tableInfo.setHavaDateTime(havaDateTime);
            tableInfo.setHaveDate(haveDate);
            tableInfo.setHaveBigDecimal(havBigDecimal);
            tableInfo.setExtendList(extendList);
        }catch (Exception e){
            LOGGER.error("读取属性失败");
        }finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return fieldInfoList;
    }

    //读index
    private static void getKeyIndexInfo(TableInfo tableInfo){
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Map<String,FieldInfo> tempMap=new HashMap<>();
            for(FieldInfo info:tableInfo.getFieldInfoList()){
                tempMap.put(info.getFieldName(),info);
            }
            ps=CONN.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX,tableInfo.getTableName()));
            rs=ps.executeQuery();
            while (rs.next()){
                Map<String,List<FieldInfo>> keyIndexMap=new LinkedHashMap<>();
                String keyName=rs.getString("key_name");
                Integer nonUnique=rs.getInt("Non_unique");
                String columnName=rs.getString("Column_name");
                if(nonUnique==1){
                    continue;
                }
                List<FieldInfo> keyFieldList = tableInfo.getKeyIndexMap().get(keyName);
                if(keyFieldList==null){
                    keyFieldList=new ArrayList<>();
                    tableInfo.getKeyIndexMap().put(keyName,keyFieldList);
                }
                /*for(FieldInfo info:tableInfo.getFieldInfoList()){
                    if(info.getFieldName().equals(columnName)){
                        keyFieldList.add(info);
                    }
                }*/
                keyFieldList.add(tempMap.get(columnName));
            }
        }catch (Exception e){
            LOGGER.error("读取索引失败");
        }finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //数据库类型转java类型方法
    private static String processJavaType(String type){
        if(ArrayUtils.contains(Constants.SQL_INTEGER_TYPE,type)){
            return "Integer";
        }else if(ArrayUtils.contains(Constants.SQL_LONG_TYPE,type)){
            return "Long";
        }else if(ArrayUtils.contains(Constants.SQL_STRING_TYPE,type)){
            return "String";
        }else if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPE,type)||ArrayUtils.contains(Constants.SQL_DATE_TYPE,type)){
            return "Date";
        }else if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE,type)){
            return "BigDecimal";
        }else {
            throw new RuntimeException("无法识别的类型:"+type);
        }
    }

}
